**************************************************************************************************************** fragment
**************************************** AGGREGATE DATA DOFILE
******************************************************************************************************************************************* fragment
clear all 
set mem 3g 
set more off
set matsize 11000
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
*********************************************
use "C:\PlanChoice\multic_pol_uic.dta"
sum 
describe, fullnames
drop if plan_family!="EP1" & plan_family!="HMO" & plan_family!="POS" & plan_family!="PPO"
************************************************ 
* generate date variable 
gen year  = substr( year_mo ,1,4)
destring year, replace
gen month = substr( year_mo ,5,2)
destring month, replace
gen data_date  = ym(year, month )
format data_date %tm
********************
drop if year!=2014
drop if month!=1
keep customer_number polnbr year
gen insample2014=1
*collapse (mean) insample2014, by(customer_number polnbr)
sort customer_number polnbr
tempfile temp2014
save `temp2014'
*******************************************************************************************************************************************
*******************************************************************************************************************************************

*******************************************************************************************************************************************
*******************************************************************************************************************************************
clear all 
cd "C:\PlanChoice"
insheet using CVSPremiums2014.csv
rename minormarket minor_market
rename policynumber polnbr
tostring yearmonth, replace
gen year  = substr( yearmonth ,1,4)
destring year, replace
gen month = substr( yearmonth ,5,2)
destring month, replace
gen data_date  = ym(year, month )
format data_date %tm

drop if month!=1
g premium2014fixed = regexr(totalpremium, "," , "")
destring premium2014fixed, replace
sort customer_number polnbr
merge customer_number polnbr using `temp2014'
drop if _merge!=3
keep customer_number polnbr data_date noofelig premium2014fixed
sort customer_number  polnbr data_date
save "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles\aux_2014.dta", replace

exit


